using System;
using System.Collections;
using System.Data;
using System.IO;
using System.Net;
using System.Text.RegularExpressions;
using Dimok.GLRules.DSs;
using Dimok.Data.Database;
using Dimok.Exceptions;
using Dimok.User;

namespace Dimok.GLRules{
	public class GL{
		public const string DB_Name = @"
DECLARE @DBName sysname
SET @DBName = DB_Name()
";
		public enum GuideLinesType :int{Matrix=1,Rates=2}
		public static string GetGLRulesXML(string conn,int programID,int applID){
			try{
				return XmlEx.GetLevelsXml(conn,"SELECT * FROM GL_RulesTreeByTier("+programID+","+applID+")","ID","ParentID","MYTREE","TREENODE","ORDER BY ProgramID DESC, Position");
			}catch(Exception exc){ throw new ClientException(exc); }
		}
		public static string GetFileName(string path){
			string re = path.IndexOf("/") >= 0 ? "/" : @"\\";
			Match m = Regex.Match(path,@"[^"+re+"]+$");
			return m.Value;
		}
		public static string GetDirectoryName(string path){
			string re = path.IndexOf("/") >= 0 ? "/" : @"\\";
			Match m = Regex.Match(path,@"(.+)"+re+".+");
			return m.Groups[1].Value;
		}
		#region Loan Program
		public static Hashtable GetProgramGLs(string connGL,int programID,GuideLinesType type){
			string fieldPdf = type + "Pdf";
			string fieldUrl = type + "Url";
			string fieldPages = type + "Pages";
			string fieldText = type + "Text";
			DataRow drLPs = SQLHelperEx.ExecuteDatasetEx(connGL,
				"SELECT "+fieldPdf+",ISNULL("+fieldUrl+",'') "+fieldUrl+",ISNULL("+fieldPages+",'') "+fieldPages+",ISNULL("+fieldText+",'') "+fieldText+" FROM t_LoanProgram WHERE ID = @ID",programID).Tables[0].Rows[0];
			byte[] pdf = drLPs[fieldPdf] == DBNull.Value ? new byte[]{} : (byte[])drLPs[fieldPdf];
			string text = drLPs[fieldText] == DBNull.Value ? "" : drLPs[fieldText]+"";
			string url = drLPs[fieldUrl]+"";
			byte[] pdfNew;
			try{
				pdfNew = Documents.PDF.Decrypt(new WebClient().DownloadData(url));
				if( drLPs[fieldPages].ToString() != "" )
					pdfNew = AcrobatObjects.AcrobatClient.GetPages(pdfNew,drLPs[fieldPages]+"");
				if( pdf.Length == 0 && pdfNew.Length == 0 )return new HashTableEx("pdf",new byte[]{},"text","");
			}catch(Exception exc){
				ClientException.Publish(exc);
				pdfNew = pdf;
			}
			if( pdfNew.Length > 0 && pdfNew.Length != pdf.Length || text == "" ){
				text = string.Join(Environment.NewLine,AcrobatObjects.AcrobatClient.GetText(pdfNew));
				SQLHelperEx.UpdateDataTable(new HashTableEx(fieldPdf,pdfNew,fieldText,text),connGL,"SELECT ID,"+fieldPdf+","+fieldText+" FROM t_LoanProgram WHERE ID = @ID",programID);
				pdf = pdfNew;
			}
			return new HashTableEx("pdf",pdf,"text",text);
		}
		public static int[] GetLoanPorgrams(string conn,int bankID){
			return (int[])SQLHelperEx.ExecuteScalarArray(conn,"SELECT ID FROM v_LoanPrograms WHERE BankID=@BankID ORDER BY Position",bankID).ToArray(typeof(int));
		}
		public static GLRulesDS.t_LoanProgramDataTable GetLoanPorgram(string conn,int programID){
			return (GLRulesDS.t_LoanProgramDataTable)SQLHelperEx.ExecuteDatasetEx(
				conn,new GLRulesDS().t_LoanProgram,"SELECT * FROM t_LoanProgram WHERE ID = @ProgID",programID);
		}
		public static void UpdateLoanPorgram(string conn,GLRulesDS.t_LoanProgramRow rowLP){
			SQLHelperEx.UpdateDataTable(rowLP.Table,conn,"SELECT * FROM t_LoanProgram WHERE ID = @ProgID",rowLP.ID);
		}
		public static GLRulesDS.t_LP_MatricesDataTable GetMatrices(string conn,int programID){
			return (GLRulesDS.t_LP_MatricesDataTable)SQLHelperEx.ExecuteDatasetEx(
				conn,new GLRulesDS().t_LP_Matrices,"SELECT * FROM t_LP_Matrices WHERE LoanProgramID = @ProgID",programID);
		}
		public static void SetMatrixChanged(string conn,int programID,bool changed){
			SQLHelperEx.ExecuteNonQueryEx(conn,"UPDATE t_LoanProgram SET MatrixChanged = @Changed WHERE ID = @ProgID",changed,programID);
		}
		public static void UpdateRate(string conn,DSs.GLRulesDS.t_LP_RatesDataTable dtRates){
			SQLHelperEx.UpdateDataTable(dtRates,conn,"SELECT * FROM t_LP_Rates WHERE ID=@ID",dtRates[0].ID);
		}
		public static void UpdateRates(string conn,DSs.GLRulesDS.t_LP_RatesDataTable dtRates){
			SQLHelperEx.UpdateDataTable(dtRates,conn,"SELECT * FROM t_LP_Rates WHERE LoanProgramID=@LPID",dtRates[0].LoanProgramID);
		}
		public static DSs.GLRulesDS.t_LP_RatesDataTable ClearRates(string conn,int programID,int areaCount){
			SQLHelperEx.ExecuteScalarEx(conn,"DELETE t_LP_Rates WHERE LoanProgramID = @ProgID",programID);
			DSs.GLRulesDS.t_LP_RatesDataTable dtRates = new Dimok.GLRules.DSs.GLRulesDS().t_LP_Rates;
			while( areaCount-- > 0 )
				dtRates.Addt_LP_RatesRow(programID,new byte[]{},"");
			return dtRates;
		}
		public static byte[][] GetRateSheets(string conn,int programID,int applID){
			object state = SQLHelperEx.ExecuteScalarEx(conn,"SELECT State FROM dbo.GL_Input(@ApplID)",applID);
			if( state == null )throw new Exception("No state info for applicant id "+applID+".");
			return GetRateSheets(conn,programID,state.ToString());
		}
		private const string SQL_LP_RATES = "SELECT * FROM t_LP_Rates WHERE LoanProgramID = @ProgID AND (States LIKE '%'+@State+'%' OR States = 'XX')";
		public static byte[][] GetRateSheets(string conn,int programID,string states){
			return (byte[][])SQLHelperEx.ExecuteScalarArray(conn,SQL_LP_RATES.Replace("*","Data"),programID,states).ToArray(typeof(byte[]));
		}
		public static GLRulesDS.t_LP_RatesDataTable GetLPRate(string conn,int programID,string state){
			return (GLRulesDS.t_LP_RatesDataTable)SQLHelperEx.ExecuteDatasetEx(
				conn,new GLRulesDS().t_LP_Rates,SQL_LP_RATES,programID,state);
		}
		public static GLRulesDS.t_LP_RatesDataTable GetLPRate(string conn,int programID){
			return (GLRulesDS.t_LP_RatesDataTable)SQLHelperEx.ExecuteDatasetEx(
				conn,new GLRulesDS().t_LP_Rates,"SELECT * FROM t_LP_Rates WHERE LoanProgramID = @ProgID",programID);
		}
		public static void SetRatesChanged(string conn,int programID,bool changed){
			SQLHelperEx.ExecuteNonQueryEx(conn,"UPDATE t_LoanProgram SET RatesChanged = @Changed WHERE ID = @ProgID",changed,programID);
		}
		#endregion

		public static string[] GetTiers(string conn,int programID){
			return (string[])SQLHelperEx.ExecuteScalarArray(conn,"SELECT Name FROM dbo.GL_GetTiers(@ProgID)",programID).ToArray(typeof(string));
		}
		public static string GetAdjPath_LoanAmount(string conn){
			return SQLHelperEx.ExecuteScalarEx(conn,"SELECT dbo.CONST_ADJ_LOANAMOUNT_PATH()").ToString();
		}

		#region GetChildIDByPath
		public static int GetChildIDByPath(string conn,object programID,object rootID,string childPath,bool create){
			return GetChildIDByPath(conn,programID,rootID,childPath,create,true);
		}
		public static int GetChildIDByPath(string conn,object programID,object rootID,string childPath,bool create,bool assert){
			int id = GetChildIDByPath(conn,programID,rootID,"",childPath,create);
			if( assert && id == 0 )throw new GLChildNotFoundException(childPath);
			return id;
		}
		public static int GetChildIDByPath(string conn,object programID,string rootPath,bool create){
			return GetChildIDByPath(conn,programID,rootPath,create,true);
		}
		public static int GetChildIDByPath(string conn,object programID,string rootPath,bool create,bool assert){
			int id = GetChildIDByPath(conn,programID,null,rootPath,"",create);
			if( assert && id == 0 )throw new GLChildNotFoundException(rootPath);
			return id;
		}
		public static int GetChildIDByPath(string conn,object programID,object rootID,string rootPath,string childPath,bool create){
			object oID = SQLHelperEx.ExecSP(conn,"dbo.GL_ChildIDByPathEx",programID,rootID,rootPath,childPath);
			if( oID == DBNull.Value && create){
				string nodeName = "";
				if( childPath != null && childPath != "" ){
					nodeName = GetFileName(childPath);
					childPath = GetDirectoryName(childPath);
				}else{
					nodeName = GetFileName(rootPath);
					rootPath = GetDirectoryName(rootPath);
				}
				int parentID = GetChildIDByPath(conn,programID,rootID,rootPath,childPath,true);
				Match mField = Regex.Match(nodeName,@"(?<fieldID>\d+)\[(?<cond>[<>=]*)(?<value>[^\]]+)\]");
				string condition = mField.Success ? mField.Groups["cond"].Value == "" ? "=" : mField.Groups["cond"].Value : "";
				return (int)SQLHelperEx.ExecSP(conn,"sp__GL_InsertConditionItem",
																							parentID,
																							parentID,
																							mField.Success?int.Parse(mField.Groups["fieldID"].Value):0,
																							condition,
																							mField.Groups["value"].Value,
																							mField.Success?null:nodeName);
			}
			//if( oID == DBNull.Value )throw new Exception("Can't find "+rootPath+"\\"+childPath
			return oID == DBNull.Value ? 0 : (int)oID;
		}
		#endregion

		public static string GetAdjPath_APRs(string conn){
			return SQLHelperEx.ExecuteScalarEx(conn,"SELECT dbo.CONST_ADJ_APR_PATH()").ToString();
		}
		public static string GetAdjPath_BuyUpsDowns(string conn){
			return SQLHelperEx.ExecuteScalarEx(conn,"SELECT dbo.CONST_ADJ_BUY_UPS_DOWNS()").ToString();
		}
		
		public static void RenameChild(string conn,int programID,string childPath,string label){
			RenameChild(conn,GetChildIDByPath(conn,programID,childPath,false),label);
		}
		public static void RenameChild(string conn,int childID,string label){
			SQLHelperEx.ExecuteScalarEx(conn,"UPDATE t_GLRules SET Label=@Label WHERE ID=@ID",label,childID);
		}
		public static void DeleteChild(string conn,int programID,string childPath){
			DeleteChild(conn,GetChildIDByPath(conn,programID,childPath,false,false));
		}
		public static void DeleteChild(string conn,int childID){
			SQLHelperEx.ExecuteScalarEx(conn,"DELETE FROM t_GLRules WHERE ID=@ID",childID);
		}
		public static void DeleteChildren(string conn,int parentID){
			SQLHelperEx.ExecuteScalarEx(conn,"DELETE FROM t_GLRules WHERE ParentID=@ParentID",parentID);
		}
		public static void CopyChild(string conn,int childID,object parentID,object afterID){
			SQLHelperEx.ExecuteScalarEx(conn,"sp__GL_CopyItem",childID,parentID,afterID);
		}

		#region Column IDs
		public static int ColumnID_Adj_APR(string conn){ 
			return GetColumnID(conn,GetGLConst(conn,"CONST_APR_COLUMN_NAME").ToString());
		}
		public static int ColumnID_BrokerRebate(string conn){ 
			return GetColumnID(conn,GetGLConst(conn,"CONST_ADJ_BUYUP_COLUMN_NAME").ToString());
		}
		public static int ColumnID_Score(string conn){ 
			return GetColumnID(conn,GetGLConst(conn,"CONST_COLUMN_NAME_SCORE").ToString());
		}
		public static int ColumnID_LTV(string conn){ 
			return GetColumnID(conn,GetGLConst(conn,"CONST_COLUMN_NAME_LTV").ToString());
		}
		public static int ColumnID_CLTV(string conn){ 
			return GetColumnID(conn,GetGLConst(conn,"CONST_COLUMN_NAME_CLTV").ToString());
		}
		public static int ColumnID_DebtRatio(string conn){ 
			return GetColumnID(conn,GetGLConst(conn,"CONST_COLUMN_NAME_DEBT_RATIO").ToString());
		}
		public static int ColumnID_LoanAmount(string conn){ 
			return GetColumnID(conn,GetGLConst(conn,"CONST_COLUMN_NAME_LOAN_AMOUNT").ToString());
		}
		public static int GetColumnID(string conn,object columnName){
			try{
				if( columnName == null )columnName = "<NULL>";
				return (int)SQLHelperEx.ExecSP(conn,"dbo.GL_ColumnIDByFieldName",columnName.ToString());
			}catch(Exception exc){ throw new Exception("Column Name:"+columnName.ToString(),exc); }
		}
		#endregion

		public static object GetGLConst(string conn,string constName){ return SQLHelperEx.ExecuteScalarEx(conn,"SELECT dbo."+constName+"()");	}
		public static int GetLastChildID(string conn,int parentID){
			object id= SQLHelperEx.ExecuteScalarEx(conn,"SELECT dbo.GL_LastChildID(@ParentID)",parentID);
			return id == DBNull.Value ? 0 : (int)id;
		}
		#region UnitEnums ===============
		public static DataSet UnitEnums(int ruleID){
			string sql = "SELECT TableName FROM t_GLRules r INNER JOIN t_ColumnProperties c ON r.ColumnID = c.ID INNER JOIN t_GL_Unit u ON c.Units = u.Units WHERE (r.ID = @RuleID)";
			string tableName = SQLHelperEx.ExecuteScalarEx(new UserObject().db_GL,sql,ruleID).ToString();
			return UnitEnums(tableName);
		}
		public static DataSet UnitEnums(){return UnitEnums("");}
		public static DataSet UnitEnums(string tableName){
			UserObject uox = new UserObject();
			tableName = tableName == "" ? "NOT TableName IS NULL" : "TableName = '"+tableName+"'";
			DataRowCollection rows = SQLHelperEx.ExecuteDatasetEx(uox.db_GL,"SELECT TableName FROM t_GL_Unit WHERE "+tableName).Tables[0].Rows;
			DataSet dsEnums = new DataSet("UNIT_ENUMS");
			foreach( DataRow row in rows ){
				DataTable table = null;
				tableName = row["TableName"]+"";
				try {
					table = SQLHelperEx.ExecuteDatasetEx(SQLHelperEx.TableExists(uox.db, tableName) ? uox.db : uox.db_GL, "SELECT * FROM [" + tableName + "]").Tables[0];
					table.DataSet.Tables.Remove(table);
					table.TableName = row["TableName"].ToString();
					dsEnums.Tables.Add(table);
				} catch (System.Data.SqlClient.SqlException exc) {
					if (exc.Number != 208) throw;
				}
			}
			return dsEnums;
		}
		#endregion UnitEnums ===============
	}
}
